package com.mayikt.dao;

import com.mayikt.entity.FlightEntity;
import com.mayikt.utils.MayiktJdbcUtils;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;


public class FlightDao {

    /**
     * 查询所有的航班信息
     *
     * @return
     */
    public List<FlightEntity> getByAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1.获取连接
            connection = MayiktJdbcUtils.getConnection();
            //2.获取执行者对象
            preparedStatement = connection.prepareStatement("select * from mayikt_flight");
            resultSet = preparedStatement.executeQuery();
            ArrayList<FlightEntity> flightEntities = new ArrayList<>();
            while (resultSet.next()) {
                Integer id = resultSet.getInt("id");
                String flightId = resultSet.getString("flight_id");
                String company = resultSet.getString("company");
                String departureAirport = resultSet.getString("departure_airport");
                String arriveAirport = resultSet.getString("arrive_airport");
                Date departureTime = resultSet.getDate("departure_time");
                Date arriveTime = resultSet.getDate("arrive_time");
                String model = resultSet.getString("model");
                Integer isDelete = resultSet.getInt("is_delete");
                FlightEntity flightEntity = new FlightEntity(id, flightId, company, departureAirport, arriveAirport, departureTime, arriveTime, model,
                        isDelete);
                // 存入到集合中
                flightEntities.add(flightEntity);
            }
            return flightEntities;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            // 释放资源
            MayiktJdbcUtils.closeConnection(resultSet, preparedStatement, connection);
        }

    }

    /**
     * 根据id查询航班信息
     *
     * @return
     */
    public FlightEntity getByIdFlight(Integer id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            // 1.获取连接
            connection = MayiktJdbcUtils.getConnection();
            //2.获取执行者对象
            String sql = "SELECT* FROM mayikt_flight WHERE id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (!resultSet.next()) {
                return null;
            }
            // 连接到db查询数据
            Integer dbId = resultSet.getInt("id");
            String flightId = resultSet.getString("flight_id");
            String company = resultSet.getString("company");
            String departureAirport = resultSet.getString("departure_airport");
            String arriveAirport = resultSet.getString("arrive_airport");
            Date departureTime = resultSet.getDate("departure_time");
            Date arriveTime = resultSet.getDate("arrive_time");
            String model = resultSet.getString("model");
            Integer isDelete = resultSet.getInt("is_delete");
            FlightEntity flightEntity = new FlightEntity(dbId, flightId, company, departureAirport, arriveAirport, departureTime, arriveTime, model,
                    isDelete);
            return flightEntity;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            // 释放资源
            MayiktJdbcUtils.closeConnection(resultSet, preparedStatement, connection);
        }
    }


    // FlightDao.java 根据主键id来删除
    public int deleteFlight(Integer id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 1.获取连接
            connection = MayiktJdbcUtils.getConnection();
            //开启事务
            MayiktJdbcUtils.beginTransaction(connection);
            preparedStatement = connection.prepareStatement("DELETE FROM mayikt_flight WHERE id=?");
            preparedStatement.setInt(1, id);
            int result = preparedStatement.executeUpdate();
            MayiktJdbcUtils.commitTransaction(connection);
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            MayiktJdbcUtils.rollBackTransaction(connection);
        } finally {
            // 释放资源
            MayiktJdbcUtils.closeConnection(preparedStatement, connection);
        }
        return 0;
    }

    public int insertFlight(FlightEntity flightEntity) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 1.获取连接
            connection = MayiktJdbcUtils.getConnection();
            // 开启事务
            MayiktJdbcUtils.beginTransaction(connection);
            //2.获取执行者对象
            String sql = "INSERT INTO mayikt_flight " +
                    "(flight_id, company, departure_airport, arrive_airport, " +
                    "departure_time, arrive_time, model, is_delete) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, '0')";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, flightEntity.getFlightId());
            preparedStatement.setString(2, flightEntity.getCompany());
            preparedStatement.setString(3, flightEntity.getDepartureAirport());
            preparedStatement.setString(4, flightEntity.getArriveAirport());
            //date java.sql.date java.util.date
            //new Date(flightEntity.getDepartureTime().getTime())  将java.util.date 转化成 java.sql.date
            preparedStatement.setDate(5, new java.sql.Date(flightEntity.getDepartureTime().getTime()));
            preparedStatement.setDate(6, new java.sql.Date(flightEntity.getArriveTime().getTime()));
            preparedStatement.setString(7, flightEntity.getModel());

            int result = preparedStatement.executeUpdate();
            // 代码执行没有问题的情况下 则直接提交事务
            MayiktJdbcUtils.commitTransaction(connection);
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            // 回滚事务
            MayiktJdbcUtils.rollBackTransaction(connection);
            return 0;
        } finally {
            // 释放资源
            MayiktJdbcUtils.closeConnection(preparedStatement, connection);
        }
    }

}
